package com.question.dao.daoImpl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;


import com.question.dao.AcademyDao;
import com.question.dao.daoFactory.DaoFactory;
import com.question.domain.Academy;
import com.question.domain.Student;

import cn.itcast.jdbc.TxQueryRunner;

public class AcademyDaoImpl implements AcademyDao{

	private QueryRunner qr = new TxQueryRunner();
	
	@Override
	public ArrayList<Academy> getAcademyRank() {
		try {
			String sql = "SELECT * FROM academy ORDER BY rank";
			return (ArrayList<Academy>) qr.query(sql, new BeanListHandler<Academy>(Academy.class));
		} catch (SQLException e) {
			// TODO: handle exception
		}
		return null;
	}

	@Override
	public void reRank(Student student) {
		try {
			String sql = "UPDATE academy SET joinPersons=joinPersons+1, totalScore=totalScore+? WHERE academyName=?";
			Object[] params = {student.getScoure(),student.getAcademy()};
			qr.update(sql, params);
			sql = "UPDATE academy a,(SELECT * FROM academy WHERE academyName=?) c SET a.rank = a.rank+1 WHERE ((a.totalScore/a.joinPersons+a.joinPersons/a.totalPersons)<(c.totalScore/c.joinPersons+c.joinPersons/c.totalPersons) OR a.joinPersons=0) AND a.rank<=c.rank";
			int len = qr.update(sql, student.getAcademy());
			if(len>0){
				sql = "UPDATE academy SET rank=rank-? WHERE academyName=?";
				qr.update(sql,len,student.getAcademy());	
			}
		} catch (SQLException e) {
			// TODO: handle exception
		}
	}

	public static void main(String[] args) {
		Student stu = new Student();
		stu.setScoure(100);
		stu.setAcademy("食品院");
		AcademyDao academyDao = DaoFactory.getInstance().getAcademyDao();
		academyDao.reRank(stu);
	}
}
